function openConn(db_type){
	conn=new ActiveXObject("ADODB.Connection");//the varibe define in public_var.js
	
	var linkstr="";
	var openSuccess=false;
	
	if(db_type=="sqlserver"){
		linkstr="Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + db_schema + ";Data Source=" + db_sid;
	}
	else if(db_type=="sqlite"){
		linkstr="DSN=SQLite3 Datasource;Database=" + db_path + ";";
	}
	else if(db_type=='access'){
		linkstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + db_path + ";Persist Security Info=False"
	}
	
	if(debug){
	  alert("db_type==>" + db_type + ",linkstr==>" + linkstr);
	}
	
	try{
		openSuccess = conn.Open(linkstr);
	}
	catch(e){
		alert(e);
	}
}

function openProjConn(p_db_path){
	proj_conn=new ActiveXObject("ADODB.Connection");//the varibe define in public_var.js
	
	var linkstr="";
	var openSuccess=false;
	
	linkstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_db_path + ";Persist Security Info=False"
	
	if(debug){
	  alert("proj conn linkstr==>" + linkstr);
	}
	
	try{
		openSuccess = proj_conn.Open(linkstr);
	}
	catch(e){
		alert(e);
	}
}

function closeConn(){
	if(conn!=null){conn.Close();}
}

function closeProjConn(){
	if(proj_conn!=null){proj_conn.Close();}
}

function getMyTasks(p_res_name){
	var v_sql = "SELECT MSP_TASKS_1.TASK_NAME as TOP_TASK_NAME, MSP_TASKS.TASK_NAME, MSP_RESOURCES.RES_NAME, Format([ASSN_FINISH_DATE],'yyyy/MM/dd') AS FINISH_DATE";
	v_sql = v_sql + " FROM MSP_TASKS AS MSP_TASKS_1, (MSP_TASKS INNER JOIN MSP_ASSIGNMENTS ON (MSP_ASSIGNMENTS.TASK_UID = MSP_TASKS.TASK_UID) AND (MSP_TASKS.PROJ_ID = MSP_ASSIGNMENTS.PROJ_ID)) INNER JOIN MSP_RESOURCES ON (MSP_ASSIGNMENTS.PROJ_ID = MSP_RESOURCES.PROJ_ID) AND (MSP_ASSIGNMENTS.RES_UID = MSP_RESOURCES.RES_UID)"
	v_sql = v_sql + " WHERE (((MSP_TASKS_1.TASK_OUTLINE_NUM)=Left([MSP_TASKS].[TASK_OUTLINE_NUM],1)) AND ((MSP_RESOURCES.RES_NAME)='" + p_res_name + "') AND ((MSP_TASKS.TASK_IS_COLLAPSED)=0))";
	v_sql = v_sql + " ORDER BY Format([ASSN_FINISH_DATE],'yyyy/MM/dd')"
	
	if(debug){
		alert("getMyTasks sql ==>" + v_sql);
	}
	
	return proj_conn.Execute(v_sql);
}

function getActiveProjectList(empid){
	return conn.Execute("select * from project where (project_owner='" + empid + "' or exists(select * from project_task where project_task.project_id=project.project_id and task_owner='" + empid + "')) and in_wkf_list='Y'");
}

function getActiveProjectTaskList(emp_id,p_project_id,parent_task_id){
	if(debug){alert("getActiveProjectTaskList(emp_id,p_project_id,parent_task_id)");}
	if(debug){alert("select * from v_project_task where task_owner='" + emp_id + "' and project_id=" + p_project_id + " and parent_task_id=" + parent_task_id + " order by project_id,parent_task_id,task_seq");}
	
	var v_proj_owner = conn.Execute("select project_owner from project where project_id=" + p_project_id).Fields(0).Value;
	
	if(v_proj_owner==emp_id){
		return conn.Execute("select * from v_project_task where project_id=" + p_project_id + " and parent_task_id=" + parent_task_id + " order by project_id,parent_task_id,task_seq");
	}
	else{
		return conn.Execute("select * from v_project_task where task_owner='" + emp_id + "' and project_id=" + p_project_id + " and parent_task_id=" + parent_task_id + " order by project_id,parent_task_id,task_seq");
	}
}

function isHaveSubTask(task_id){
	var rec_cnt=0;
	
	rec_cnt = conn.Execute("select count(task_id) from project_task where parent_task_id=" + task_id).Fields(0).Value;

	return rec_cnt==0 || rec_cnt==null ? false : true;
}

function getTaskListByParentTaskId(parent_task_id){
	return conn.Execute("select * from v_project_task where parent_task_id=" + parent_task_id + " order by task_seq");
}

function updateTaskCompletion(p_task_id){
	var rs,proj_id;
	
	conn.Execute("update project_task set completion_of='" + document.all.item('t' + p_task_id).value + "' where task_id=" + p_task_id);
	rs = conn.Execute("select parent_task_id,avg(completion_of) from project_task group by parent_task_id having parent_task_id=(select parent_task_id from project_task where task_id=" + p_task_id + ")");
	
	while(rs.Fields(0).Value!="0"){
		conn.Execute("update project_task set completion_of=" + rs.Fields(1).Value + " where task_id=" + rs.Fields(0).Value);
		rs = conn.Execute("select parent_task_id,avg(completion_of) from project_task group by parent_task_id having parent_task_id=(select parent_task_id from project_task where task_id=" + rs.Fields(0).Value + ")");
	}
	
	proj_id=conn.Execute("select project_id from project_task where task_id=" + p_task_id).Fields(0).Value;
	rs = conn.Execute("select avg(completion_of) from project_task where project_id=" + proj_id + " group by parent_task_id having parent_task_id=0");
	conn.Execute("update project set completion_of=" + rs.Fields(0).Value + " where project_id=" + proj_id);
}

function insertToDoList(p_todo_date,p_todo_things){
	conn.Execute("insert into to_do_list(todo_date,todo_things) values('" + p_todo_date + "','" + p_todo_things + "')");
}

function updateToDoList(p_todo_id,p_todo_date,p_todo_things,p_todo_completion_of){
	conn.Execute("update to_do_list set todo_date='" + p_todo_date + "',todo_things='" + p_todo_things + "',completion_of='" + p_todo_completion_of + "'  where todo_id=" + p_todo_id);
}

function deleteToDoList(p_todo_id){
	conn.Execute("delete from to_do_list where todo_id=" + p_todo_id);
}

function getTodoList(p_condition,p_dateOffsetValue){
	var sql="select * from to_do_list where completion_of<>100 ";
	
	if(p_condition!='un-completion'){
		sql+="or todo_date>#" + dateDiff(new Date(),p_dateOffsetValue,"d").format('yyyy/MM/dd') + "# "
	}
	
	sql+="order by todo_date";

	return conn.Execute(sql);
}

function getMemoList(){
	return conn.Execute("select * from [memo]");
}

function updateMemo(p_uid,p_memo_content){
	var v_sql;
	
	if(p_uid=='0'){
		v_sql = "insert into [memo](memo_content) values('" + p_memo_content + "')";
	}
	else{
		v_sql="update [memo] set memo_content='" + p_memo_content + "' where uid=" + p_uid
	}
	
	if(debug){alert("update Memo SQL==>" + v_sql);}
	conn.Execute(v_sql);
}